USE AdventureWorks2008
GO

CREATE PROC AddTerritory(@mgrid int, @territoryid int, @t_name varchar(50)) 
AS 
BEGIN
   DECLARE @mOrgNode hierarchyid, @lc hierarchyid
   SELECT @mOrgNode = OrgNode 
   FROM Sales.SalesTerritoryOrg 
   WHERE TerritoryID = @mgrid
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   BEGIN TRANSACTION
      SELECT @lc = max(OrgNode) 
      FROM Sales.SalesTerritoryOrg 
      WHERE OrgNode.GetAncestor(1) =@mOrgNode ;

      INSERT INTO Sales.SalesTerritoryOrg (OrgNode, TerritoryID, TerritoryName)
      VALUES(@mOrgNode.GetDescendant(@lc, NULL), @territoryid, @t_name)
   COMMIT
END ;
GO
